﻿using System;
using System.Data;
using System.Data.DBLib;
using SysComm;

namespace SysOperationLayer.DBHandle
{
    /// <summary>
    /// 此类对应于dbf_updates
    /// </summary>
    public class UpdatesDB : DBBase
    {
        public DataTable getUpdatesList(string tablename, string fromTime, string toTime)
        {
            string strSql = "";
            if (ConnectDB())
            {
                strSql = "select * from DBF_UPDATES where 1=1";

                if (tablename != "" && tablename != null)
                {
                    strSql += " and TableName='" + tablename + "'";
                }
                if (fromTime != "" && toTime != "")
                {
                    strSql += " and UpdatedTime between '" + fromTime + "' and '" + toTime + "'"; ;
                }

                strSql += " order by rowno desc";
                return m_objDB.getDataTable(strSql, "DBF_UPDATES");
            }
            m_strMessage = "Cannot Connect to the Database!";
            return null;
        }
/*
select 'Project' as TableName , ProjectCode as ReferenceID, CreateBy, CreateDate, CreateIP from Project
select 'ProjectDocument' as TableName, ProjectCode as ReferenceID, CreateBy, CreateDate, CreateIP from ProjectDocument
select 'ProjectGroup' as TableName, ProjectGroupID as ReferenceID, CreateBy, CreateDate, CreateIP from ProjectGroup
select 'ProjectExpenses' as TableName, ProjectCode as ReferenceID, CreateBy, CreateDate, CreateIP from ProjectExpenses
select 'ProjectClass' as TableName, ProjectClass as ReferenceID, CreateBy, CreateDate, CreateIP from ProjectClass
select 'Agent' as TableName, Agent as ReferenceID, CreateBy, CreateDate, CreateIP from Agent
select 'Client' as TableName, Client as ReferenceID, CreateBy, CreateDate, CreateIP from Client
select 'Country' as TableName, Country as ReferenceID, CreateBy, CreateDate, CreateIP from Country

select 'Project' as TableName , ProjectCode as ReferenceID, DeleteBy, DeleteDate, DeleteIP from Project
select 'ProjectDocument' as TableName, ProjectCode as ReferenceID, DeleteBy, DeleteDate, DeleteIP from ProjectDocument
select 'ProjectGroup' as TableName, ProjectGroupID as ReferenceID, DeleteBy, DeleteDate, DeleteIP from ProjectGroup
select 'ProjectExpenses' as TableName, ProjectCode as ReferenceID, DeleteBy, DeleteDate, DeleteIP from ProjectExpenses
select 'ProjectClass' as TableName, ProjectClass as ReferenceID, DeleteBy, DeleteDate, DeleteIP from ProjectClass
select 'Agent' as TableName, Agent as ReferenceID, DeleteBy, DeleteDate, DeleteIP from Agent
select 'Client' as TableName, Client as ReferenceID, DeleteBy, DeleteDate, DeleteIP from Client
select 'Country' as TableName, Country as ReferenceID, DeleteBy, DeleteDate, DeleteIP from Country
*/
        public DataTable getProjectInsertsList(string fromTime, string toTime)
        {
            string strSql = "";
            if (ConnectDB())
            {
                strSql = "select 'Project' as TableName , ProjectCode as ReferenceID, CreateBy, CreateDate, CreateIP from Project where 1=1";

                if (fromTime != "" && toTime != "")
                {
                    strSql += " and CreateDate between '" + fromTime + "' and '" + toTime + "'"; ;
                }
                strSql += " order by CreateDate desc";
                return m_objDB.getDataTable(strSql, "Project");
            }
            m_strMessage = "Cannot Connect to the Database!";
            return null;
        }

        public DataTable getProjectDeletesList(string fromTime, string toTime)
        {
            string strSql = "";
            if (ConnectDB())
            {
                strSql = "select 'Project' as TableName , ProjectCode as ReferenceID, DeleteBy, DeleteDate, DeleteIP from Project where IsDelete=1";

                if (fromTime != "" && toTime != "")
                {
                    strSql += " and DeleteDate between '" + fromTime + "' and '" + toTime + "'"; ;
                }
                strSql += " order by DeleteDate desc";
                return m_objDB.getDataTable(strSql, "Project");
            }
            m_strMessage = "Cannot Connect to the Database!";
            return null; 
        }

        public DataTable getProjectDocumentInsertsList(string fromTime, string toTime)
        {
            string strSql = "";
            if (ConnectDB())
            {
                strSql = "select 'ProjectDocument' as TableName, ProjectCode as ReferenceID, CreateBy, CreateDate, CreateIP from ProjectDocument where 1=1";

                if (fromTime != "" && toTime != "")
                {
                    strSql += " and CreateDate between '" + fromTime + "' and '" + toTime + "'"; ;
                }
                strSql += " order by CreateDate desc";
                return m_objDB.getDataTable(strSql, "ProjectDocument");
            }
            m_strMessage = "Cannot Connect to the Database!";
            return null;
        }

        public DataTable getProjectDocumentDeletesList(string fromTime, string toTime)
        {
            string strSql = "";
            if (ConnectDB())
            {
                strSql = "select 'ProjectDocument' as TableName, ProjectCode as ReferenceID, DeleteBy, DeleteDate, DeleteIP from ProjectDocument where IsDelete=1";

                if (fromTime != "" && toTime != "")
                {
                    strSql += " and DeleteDate between '" + fromTime + "' and '" + toTime + "'"; ;
                }
                strSql += " order by DeleteDate desc";
                return m_objDB.getDataTable(strSql, "ProjectDocument");
            }
            m_strMessage = "Cannot Connect to the Database!";
            return null;
        }

        public DataTable getProjectGroupInsertsList(string fromTime, string toTime)
        {
            string strSql = "";
            if (ConnectDB())
            {
                strSql = "select 'ProjectGroup' as TableName, ProjectGroupID as ReferenceID, CreateBy, CreateDate, CreateIP from ProjectGroup where 1=1";

                if (fromTime != "" && toTime != "")
                {
                    strSql += " and CreateDate between '" + fromTime + "' and '" + toTime + "'"; ;
                }
                strSql += " order by CreateDate desc";
                return m_objDB.getDataTable(strSql, "ProjectGroup");
            }
            m_strMessage = "Cannot Connect to the Database!";
            return null;
        }

        public DataTable getProjectGroupDeletesList(string fromTime, string toTime)
        {
            string strSql = "";
            if (ConnectDB())
            {
                strSql = "select 'ProjectGroup' as TableName, ProjectGroupID as ReferenceID, DeleteBy, DeleteDate, DeleteIP from ProjectGroup where IsDelete=1";

                if (fromTime != "" && toTime != "")
                {
                    strSql += " and DeleteDate between '" + fromTime + "' and '" + toTime + "'"; ;
                }
                strSql += " order by DeleteDate desc";
                return m_objDB.getDataTable(strSql, "ProjectGroup");
            }
            m_strMessage = "Cannot Connect to the Database!";
            return null;
        }

        public DataTable getProjectExpensesInsertsList(string fromTime, string toTime)
        {
            string strSql = "";
            if (ConnectDB())
            {
                strSql = "select 'ProjectExpenses' as TableName, ProjectCode as ReferenceID, CreateBy, CreateDate, CreateIP from ProjectExpenses where 1=1";

                if (fromTime != "" && toTime != "")
                {
                    strSql += " and CreateDate between '" + fromTime + "' and '" + toTime + "'"; ;
                }
                strSql += " order by CreateDate desc";
                return m_objDB.getDataTable(strSql, "ProjectExpenses");
            }
            m_strMessage = "Cannot Connect to the Database!";
            return null;
        }

        public DataTable getProjectExpensesDeletesList(string fromTime, string toTime)
        {
            string strSql = "";
            if (ConnectDB())
            {
                strSql = "select 'ProjectExpenses' as TableName, ProjectCode as ReferenceID, DeleteBy, DeleteDate, DeleteIP from ProjectExpenses where IsDelete=1";

                if (fromTime != "" && toTime != "")
                {
                    strSql += " and DeleteDate between '" + fromTime + "' and '" + toTime + "'"; ;
                }
                strSql += " order by DeleteDate desc";
                return m_objDB.getDataTable(strSql, "ProjectExpenses");
            }
            m_strMessage = "Cannot Connect to the Database!";
            return null;
        }

        public DataTable getProjectClassInsertsList(string fromTime, string toTime)
        {
            string strSql = "";
            if (ConnectDB())
            {
                strSql = "select 'ProjectClass' as TableName, ProjectClass as ReferenceID, CreateBy, CreateDate, CreateIP from ProjectClass where 1=1";

                if (fromTime != "" && toTime != "")
                {
                    strSql += " and CreateDate between '" + fromTime + "' and '" + toTime + "'"; ;
                }
                strSql += " order by CreateDate desc";
                return m_objDB.getDataTable(strSql, "ProjectClass");
            }
            m_strMessage = "Cannot Connect to the Database!";
            return null;
        }

        public DataTable getProjectClassDeletesList(string fromTime, string toTime)
        {
            string strSql = "";
            if (ConnectDB())
            {
                strSql = "select 'ProjectClass' as TableName, ProjectClass as ReferenceID, DeleteBy, DeleteDate, DeleteIP from ProjectClass where IsDelete=1";

                if (fromTime != "" && toTime != "")
                {
                    strSql += " and DeleteDate between '" + fromTime + "' and '" + toTime + "'"; ;
                }
                strSql += " order by DeleteDate desc";
                return m_objDB.getDataTable(strSql, "ProjectClass");
            }
            m_strMessage = "Cannot Connect to the Database!";
            return null;
        }

        public DataTable getAgentInsertsList(string fromTime, string toTime)
        {
            string strSql = "";
            if (ConnectDB())
            {
                strSql = "select 'Agent' as TableName, Agent as ReferenceID, CreateBy, CreateDate, CreateIP from Agent where 1=1";

                if (fromTime != "" && toTime != "")
                {
                    strSql += " and CreateDate between '" + fromTime + "' and '" + toTime + "'"; ;
                }
                strSql += " order by CreateDate desc";
                return m_objDB.getDataTable(strSql, "Agent");
            }
            m_strMessage = "Cannot Connect to the Database!";
            return null;
        }

        public DataTable getAgentDeletesList(string fromTime, string toTime)
        {
            string strSql = "";
            if (ConnectDB())
            {
                strSql = "select 'Agent' as TableName, Agent as ReferenceID, DeleteBy, DeleteDate, DeleteIP from Agent where IsDelete=1";

                if (fromTime != "" && toTime != "")
                {
                    strSql += " and DeleteDate between '" + fromTime + "' and '" + toTime + "'"; ;
                }
                strSql += " order by DeleteDate desc";
                return m_objDB.getDataTable(strSql, "Agent");
            }
            m_strMessage = "Cannot Connect to the Database!";
            return null;
        }

        public DataTable getClientInsertsList(string fromTime, string toTime)
        {
            string strSql = "";
            if (ConnectDB())
            {
                strSql = "select 'Client' as TableName, Client as ReferenceID, CreateBy, CreateDate, CreateIP from Client where 1=1";

                if (fromTime != "" && toTime != "")
                {
                    strSql += " and CreateDate between '" + fromTime + "' and '" + toTime + "'"; ;
                }
                strSql += " order by CreateDate desc";
                return m_objDB.getDataTable(strSql, "Client");
            }
            m_strMessage = "Cannot Connect to the Database!";
            return null;
        }

        public DataTable getClientDeletesList(string fromTime, string toTime)
        {
            string strSql = "";
            if (ConnectDB())
            {
                strSql = "select 'Client' as TableName, Client as ReferenceID, DeleteBy, DeleteDate, DeleteIP from Client where IsDelete=1";

                if (fromTime != "" && toTime != "")
                {
                    strSql += " and DeleteDate between '" + fromTime + "' and '" + toTime + "'"; ;
                }
                strSql += " order by DeleteDate desc";
                return m_objDB.getDataTable(strSql, "Client");
            }
            m_strMessage = "Cannot Connect to the Database!";
            return null;
        }

        public DataTable getCountryInsertsList(string fromTime, string toTime)
        {
            string strSql = "";
            if (ConnectDB())
            {
                strSql = "select 'Country' as TableName, Country as ReferenceID, CreateBy, CreateDate, CreateIP from Country where 1=1";

                if (fromTime != "" && toTime != "")
                {
                    strSql += " and CreateDate between '" + fromTime + "' and '" + toTime + "'"; ;
                }
                strSql += " order by CreateDate desc";
                return m_objDB.getDataTable(strSql, "Country");
            }
            m_strMessage = "Cannot Connect to the Database!";
            return null;
        }

        public DataTable getCountryDeletesList(string fromTime, string toTime)
        {
            string strSql = "";
            if (ConnectDB())
            {
                strSql = "select 'Country' as TableName, Country as ReferenceID, DeleteBy, DeleteDate, DeleteIP from Country where IsDelete=1";

                if (fromTime != "" && toTime != "")
                {
                    strSql += " and DeleteDate between '" + fromTime + "' and '" + toTime + "'"; ;
                }
                strSql += " order by DeleteDate desc";
                return m_objDB.getDataTable(strSql, "Country");
            }
            m_strMessage = "Cannot Connect to the Database!";
            return null;
        }


        public void copyDataTable(DataTable src, DataTable dest)
        {
            if (src != null && dest != null)
            {
                object[] obj = new object[src.Columns.Count];
                for (int i = 0; i < src.Rows.Count; i++)
                {
                    src.Rows[i].ItemArray.CopyTo(obj, 0);
                    dest.Rows.Add(obj);
                }
            }
        }

        public DataTable getInsertsList(string tablename, string fromTime, string toTime)
        {
            DataTable dtResult = new DataTable("InsertResults");
            DataTable dtReturn;

            if (ConnectDB())
            {
                //select 'Project' as TableName , ProjectCode as ReferenceID, CreateBy, CreateDate, CreateIP from Project
                dtResult.Columns.Add("TableName");
                dtResult.Columns.Add("ReferenceID");
                dtResult.Columns.Add("CreateBy");
                dtResult.Columns.Add("CreateDate");
                dtResult.Columns.Add("CreateIP");
                if (tablename == "" || tablename == null)
                {
                    //查找所有表
                    dtReturn = getProjectInsertsList(fromTime, toTime);
                    copyDataTable(dtReturn, dtResult);
                    dtReturn = getProjectDocumentInsertsList(fromTime, toTime);
                    copyDataTable(dtReturn, dtResult);
                    dtReturn = getProjectGroupInsertsList(fromTime, toTime);
                    copyDataTable(dtReturn, dtResult);
                    dtReturn = getProjectExpensesInsertsList(fromTime, toTime);
                    copyDataTable(dtReturn, dtResult);
                    dtReturn = getProjectClassInsertsList(fromTime, toTime);
                    copyDataTable(dtReturn, dtResult);
                    dtReturn = getAgentInsertsList(fromTime, toTime);
                    copyDataTable(dtReturn, dtResult);
                    dtReturn = getClientInsertsList(fromTime, toTime);
                    copyDataTable(dtReturn, dtResult);
                    dtReturn = getCountryInsertsList(fromTime, toTime);
                    copyDataTable(dtReturn, dtResult);
                }
                else
                {
                    if (tablename == SysUtil.m_table_project)
                    {
                        dtResult = getProjectInsertsList(fromTime, toTime);
                    }
                    else if (tablename == SysUtil.m_table_project_group)
                    {
                        dtResult = getProjectGroupInsertsList(fromTime, toTime);
                    }
                    else if (tablename == SysUtil.m_table_project_document)
                    {
                        dtResult = getProjectDocumentInsertsList(fromTime, toTime);
                    }
                    else if (tablename == SysUtil.m_table_project_expenses)
                    {
                        dtResult = getProjectExpensesInsertsList(fromTime, toTime);
                    }
                    else if (tablename == SysUtil.m_table_project_class)
                    {
                        dtResult = getProjectClassInsertsList(fromTime, toTime);
                    }
                    else if (tablename == SysUtil.m_table_agent)
                    {
                        dtResult = getAgentInsertsList(fromTime, toTime);
                    }
                    else if (tablename == SysUtil.m_table_client)
                    {
                        dtResult = getClientInsertsList(fromTime, toTime);
                    }
                    else if (tablename == SysUtil.m_table_country)
                    {
                        dtResult = getCountryInsertsList(fromTime, toTime);
                    }
                }
                return dtResult;
            }
            m_strMessage = "Cannot Connect to the Database!";
            return null;
        }


        public DataTable getDeleteList(string tablename, string fromTime, string toTime)
        {
            DataTable dtResult = new DataTable("DeleteResults");
            DataTable dtReturn;

            if (ConnectDB())
            {
                dtResult.Columns.Add("TableName");
                dtResult.Columns.Add("ReferenceID");
                dtResult.Columns.Add("DeleteBy");
                dtResult.Columns.Add("DeleteDate");
                dtResult.Columns.Add("DeleteIP");
                if (tablename == "" || tablename == null)
                {
                    //查找所有表
                    dtReturn = getProjectDeletesList(fromTime, toTime);
                    copyDataTable(dtReturn, dtResult);
                    dtReturn = getProjectDocumentDeletesList(fromTime, toTime);
                    copyDataTable(dtReturn, dtResult);
                    dtReturn = getProjectGroupDeletesList(fromTime, toTime);
                    copyDataTable(dtReturn, dtResult);
                    dtReturn = getProjectExpensesDeletesList(fromTime, toTime);
                    copyDataTable(dtReturn, dtResult);
                    dtReturn = getProjectClassDeletesList(fromTime, toTime);
                    copyDataTable(dtReturn, dtResult);
                    dtReturn = getAgentDeletesList(fromTime, toTime);
                    copyDataTable(dtReturn, dtResult);
                    dtReturn = getClientDeletesList(fromTime, toTime);
                    copyDataTable(dtReturn, dtResult);
                    dtReturn = getCountryDeletesList(fromTime, toTime);
                    copyDataTable(dtReturn, dtResult);
                }
                else
                {
                    if (tablename == SysUtil.m_table_project)
                    {
                        dtResult = getProjectDeletesList(fromTime, toTime);
                    }
                    else if (tablename == SysUtil.m_table_project_group)
                    {
                        dtResult = getProjectGroupDeletesList(fromTime, toTime);
                    }
                    else if (tablename == SysUtil.m_table_project_document)
                    {
                        dtResult = getProjectDocumentDeletesList(fromTime, toTime);
                    }
                    else if (tablename == SysUtil.m_table_project_expenses)
                    {
                        dtResult = getProjectExpensesDeletesList(fromTime, toTime);
                    }
                    else if (tablename == SysUtil.m_table_project_class)
                    {
                        dtResult = getProjectClassDeletesList(fromTime, toTime);
                    }
                    else if (tablename == SysUtil.m_table_agent)
                    {
                        dtResult = getAgentDeletesList(fromTime, toTime);
                    }
                    else if (tablename == SysUtil.m_table_client)
                    {
                        dtResult = getClientDeletesList(fromTime, toTime);
                    }
                    else if (tablename == SysUtil.m_table_country)
                    {
                        dtResult = getCountryDeletesList(fromTime, toTime);
                    }
                }
                return dtResult;
            }
            m_strMessage = "Cannot Connect to the Database!";
            return null;
        }

        public DataTable getUpdatesTableList()
        {
            string strSql = "";
            if (ConnectDB())
            {
                strSql = "select distinct(TableName) from dbf_updates";
                return m_objDB.getDataTable(strSql, "DBF_UPDATES_TABLE_NAME");
            }
            m_strMessage = "Cannot Connect to the Database!";
            return null;
        }
    }
}
